package com.voicecomm.database;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

/**
 * 数据库管理器
 * 负责数据库连接管理和通联日志的CRUD操作
 * 
 * @author VoiceComm Team
 * @version 1.0.0
 */
public class DatabaseManager {
    
    private static final Logger logger = LoggerFactory.getLogger(DatabaseManager.class);
    
    private HikariDataSource dataSource;
    private final DatabaseConfig config;
    
    // SQL语句常量
    private static final String CREATE_TABLE_SQL = "CREATE TABLE IF NOT EXISTS communication_logs (\n" +
            "            id BIGINT AUTO_INCREMENT PRIMARY KEY,\n" +
            "            session_id VARCHAR(100) NOT NULL UNIQUE,\n" +
            "            caller_id VARCHAR(50) NOT NULL,\n" +
            "            receiver_id VARCHAR(50) NOT NULL,\n" +
            "            start_time DATETIME NOT NULL,\n" +
            "            end_time DATETIME,\n" +
            "            duration_seconds INT,\n" +
            "            recognition_text TEXT,\n" +
            "            confidence_score DOUBLE,\n" +
            "            audio_file_path VARCHAR(500),\n" +
            "            status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',\n" +
            "            notes TEXT,\n" +
            "            qth VARCHAR(255),\n" +
            "            device_model VARCHAR(255),\n" +
            "            signal_report VARCHAR(50),\n" +
            "            antenna_type VARCHAR(255),\n" +
            "            antenna_height_m DOUBLE,\n" +
            "            created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,\n" +
            "            updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,\n" +
            "            INDEX idx_session_id (session_id),\n" +
            "            INDEX idx_caller_id (caller_id),\n" +
            "            INDEX idx_receiver_id (receiver_id),\n" +
            "            INDEX idx_start_time (start_time),\n" +
            "            INDEX idx_status (status)\n" +
            "        )";
    
    private static final String INSERT_SQL = "INSERT INTO communication_logs (session_id, caller_id, receiver_id, start_time, \n" +
            "                                      end_time, duration_seconds, recognition_text, \n" +
            "                                      confidence_score, audio_file_path, status, notes, \n" +
            "                                      qth, device_model, signal_report, antenna_type, antenna_height_m)\n" +
            "        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    
    private static final String UPDATE_SQL = "UPDATE communication_logs \n" +
            "        SET end_time = ?, duration_seconds = ?, recognition_text = ?, \n" +
            "            confidence_score = ?, audio_file_path = ?, status = ?, notes = ?, \n" +
            "            qth = ?, device_model = ?, signal_report = ?, antenna_type = ?, antenna_height_m = ?, \n" +
            "            updated_at = ?\n" +
            "        WHERE session_id = ?";
    
    private static final String SELECT_BY_SESSION_SQL = "SELECT * FROM communication_logs WHERE session_id = ?";
    
    private static final String SELECT_ACTIVE_SQL = "SELECT * FROM communication_logs WHERE status = 'ACTIVE' ORDER BY start_time DESC";
    
    private static final String SELECT_BY_DATE_RANGE_SQL = "SELECT * FROM communication_logs \n" +
            "        WHERE start_time BETWEEN ? AND ? \n" +
            "        ORDER BY start_time DESC";
    
    private static final String DELETE_SQL = "DELETE FROM communication_logs WHERE session_id = ?";
    
    /**
     * 构造函数
     * 
     * @param config 数据库配置
     */
    public DatabaseManager(DatabaseConfig config) {
        this.config = config;
        initializeDataSource();
        createTableIfNotExists();
    }
    
    /**
     * 初始化数据源
     */
    private void initializeDataSource() {
        try {
            HikariConfig hikariConfig = new HikariConfig();
            hikariConfig.setJdbcUrl(config.getJdbcUrl());
            hikariConfig.setUsername(config.getUsername());
            hikariConfig.setPassword(config.getPassword());
            hikariConfig.setDriverClassName(config.getDriverClassName());
            
            // 连接池配置
            hikariConfig.setMaximumPoolSize(config.getMaxPoolSize());
            hikariConfig.setMinimumIdle(config.getMinIdle());
            hikariConfig.setConnectionTimeout(config.getConnectionTimeout());
            hikariConfig.setIdleTimeout(config.getIdleTimeout());
            hikariConfig.setMaxLifetime(config.getMaxLifetime());
            hikariConfig.setLeakDetectionThreshold(config.getLeakDetectionThreshold());
            
            // 连接池名称
            hikariConfig.setPoolName("VoiceCommPool");
            
            dataSource = new HikariDataSource(hikariConfig);
            
            logger.info("数据库连接池初始化成功: {}", config.getJdbcUrl());
            
        } catch (Exception e) {
            logger.error("初始化数据库连接池失败", e);
            throw new RuntimeException("数据库初始化失败", e);
        }
    }
    
    /**
     * 创建表（如果不存在）
     */
    private void createTableIfNotExists() {
        try (Connection connection = dataSource.getConnection();
             Statement statement = connection.createStatement()) {
            
            statement.execute(CREATE_TABLE_SQL);
            logger.info("数据库表创建成功或已存在");

            // 尝试进行列迁移（幂等）
            try {
                statement.execute("ALTER TABLE communication_logs ADD COLUMN qth VARCHAR(255)");
            } catch (SQLException ignored) {}
            try {
                statement.execute("ALTER TABLE communication_logs ADD COLUMN device_model VARCHAR(255)");
            } catch (SQLException ignored) {}
            try {
                statement.execute("ALTER TABLE communication_logs ADD COLUMN signal_report VARCHAR(50)");
            } catch (SQLException ignored) {}
            try {
                statement.execute("ALTER TABLE communication_logs ADD COLUMN antenna_type VARCHAR(255)");
            } catch (SQLException ignored) {}
            try {
                statement.execute("ALTER TABLE communication_logs ADD COLUMN antenna_height_m DOUBLE");
            } catch (SQLException ignored) {}
            
        } catch (SQLException e) {
            logger.error("创建数据库表失败", e);
            throw new RuntimeException("创建数据库表失败", e);
        }
    }
    
    /**
     * 保存通联日志
     * 
     * @param log 通联日志对象
     * @return 保存后的日志ID
     */
    public Long saveCommunicationLog(CommunicationLog log) {
        try (Connection connection = dataSource.getConnection();
             PreparedStatement statement = connection.prepareStatement(INSERT_SQL, Statement.RETURN_GENERATED_KEYS)) {
            
            statement.setString(1, log.getSessionId());
            statement.setString(2, log.getCallerId());
            statement.setString(3, log.getReceiverId());
            statement.setTimestamp(4, Timestamp.valueOf(log.getStartTime()));
            statement.setTimestamp(5, log.getEndTime() != null ? Timestamp.valueOf(log.getEndTime()) : null);
            statement.setInt(6, log.getDurationSeconds() != null ? log.getDurationSeconds() : 0);
            statement.setString(7, log.getRecognitionText());
            statement.setDouble(8, log.getConfidenceScore() != null ? log.getConfidenceScore() : 0.0);
            statement.setString(9, log.getAudioFilePath());
            statement.setString(10, log.getStatus());
            statement.setString(11, log.getNotes());
            statement.setString(12, log.getQth());
            statement.setString(13, log.getDeviceModel());
            statement.setString(14, log.getSignalReport());
            statement.setString(15, log.getAntennaType());
            if (log.getAntennaHeightMeters() != null) {
                statement.setDouble(16, log.getAntennaHeightMeters());
            } else {
                statement.setNull(16, Types.DOUBLE);
            }
            
            int affectedRows = statement.executeUpdate();
            
            if (affectedRows > 0) {
                try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
                    if (generatedKeys.next()) {
                        Long id = generatedKeys.getLong(1);
                        log.setId(id);
                        logger.debug("通联日志保存成功，ID: {}", id);
                        return id;
                    }
                }
            }
            
            throw new SQLException("保存通联日志失败，未返回生成的ID");
            
        } catch (SQLException e) {
            logger.error("保存通联日志失败", e);
            throw new RuntimeException("保存通联日志失败", e);
        }
    }
    
    /**
     * 更新通联日志
     * 
     * @param log 通联日志对象
     * @return 更新的行数
     */
    public int updateCommunicationLog(CommunicationLog log) {
        try (Connection connection = dataSource.getConnection();
             PreparedStatement statement = connection.prepareStatement(UPDATE_SQL)) {
            
            statement.setTimestamp(1, log.getEndTime() != null ? Timestamp.valueOf(log.getEndTime()) : null);
            statement.setInt(2, log.getDurationSeconds() != null ? log.getDurationSeconds() : 0);
            statement.setString(3, log.getRecognitionText());
            statement.setDouble(4, log.getConfidenceScore() != null ? log.getConfidenceScore() : 0.0);
            statement.setString(5, log.getAudioFilePath());
            statement.setString(6, log.getStatus());
            statement.setString(7, log.getNotes());
            statement.setString(8, log.getQth());
            statement.setString(9, log.getDeviceModel());
            statement.setString(10, log.getSignalReport());
            statement.setString(11, log.getAntennaType());
            if (log.getAntennaHeightMeters() != null) {
                statement.setDouble(12, log.getAntennaHeightMeters());
            } else {
                statement.setNull(12, Types.DOUBLE);
            }
            statement.setTimestamp(13, Timestamp.valueOf(LocalDateTime.now()));
            statement.setString(14, log.getSessionId());
            
            int affectedRows = statement.executeUpdate();
            logger.debug("通联日志更新完成，影响行数: {}", affectedRows);
            return affectedRows;
            
        } catch (SQLException e) {
            logger.error("更新通联日志失败", e);
            throw new RuntimeException("更新通联日志失败", e);
        }
    }
    
    /**
     * 根据会话ID查找通联日志
     * 
     * @param sessionId 会话ID
     * @return 通联日志对象，如果不存在则返回空
     */
    public Optional<CommunicationLog> findBySessionId(String sessionId) {
        try (Connection connection = dataSource.getConnection();
             PreparedStatement statement = connection.prepareStatement(SELECT_BY_SESSION_SQL)) {
            
            statement.setString(1, sessionId);
            
            try (ResultSet resultSet = statement.executeQuery()) {
                if (resultSet.next()) {
                    return Optional.of(mapResultSetToLog(resultSet));
                }
            }
            
        } catch (SQLException e) {
            logger.error("根据会话ID查找通联日志失败", e);
            throw new RuntimeException("查找通联日志失败", e);
        }
        
        return Optional.empty();
    }
    
    /**
     * 查找所有活跃的通联日志
     * 
     * @return 活跃的通联日志列表
     */
    public List<CommunicationLog> findActiveLogs() {
        List<CommunicationLog> logs = new ArrayList<>();
        
        try (Connection connection = dataSource.getConnection();
             PreparedStatement statement = connection.prepareStatement(SELECT_ACTIVE_SQL);
             ResultSet resultSet = statement.executeQuery()) {
            
            while (resultSet.next()) {
                logs.add(mapResultSetToLog(resultSet));
            }
            
        } catch (SQLException e) {
            logger.error("查找活跃通联日志失败", e);
            throw new RuntimeException("查找活跃通联日志失败", e);
        }
        
        return logs;
    }
    
    /**
     * 根据日期范围查找通联日志
     * 
     * @param startDate 开始日期
     * @param endDate 结束日期
     * @return 通联日志列表
     */
    public List<CommunicationLog> findByDateRange(LocalDateTime startDate, LocalDateTime endDate) {
        List<CommunicationLog> logs = new ArrayList<>();
        
        try (Connection connection = dataSource.getConnection();
             PreparedStatement statement = connection.prepareStatement(SELECT_BY_DATE_RANGE_SQL)) {
            
            statement.setTimestamp(1, Timestamp.valueOf(startDate));
            statement.setTimestamp(2, Timestamp.valueOf(endDate));
            
            try (ResultSet resultSet = statement.executeQuery()) {
                while (resultSet.next()) {
                    logs.add(mapResultSetToLog(resultSet));
                }
            }
            
        } catch (SQLException e) {
            logger.error("根据日期范围查找通联日志失败", e);
            throw new RuntimeException("查找通联日志失败", e);
        }
        
        return logs;
    }
    
    /**
     * 删除通联日志
     * 
     * @param sessionId 会话ID
     * @return 删除的行数
     */
    public int deleteCommunicationLog(String sessionId) {
        try (Connection connection = dataSource.getConnection();
             PreparedStatement statement = connection.prepareStatement(DELETE_SQL)) {
            
            statement.setString(1, sessionId);
            int affectedRows = statement.executeUpdate();
            
            logger.debug("删除通联日志完成，影响行数: {}", affectedRows);
            return affectedRows;
            
        } catch (SQLException e) {
            logger.error("删除通联日志失败", e);
            throw new RuntimeException("删除通联日志失败", e);
        }
    }
    
    /**
     * 将ResultSet映射为CommunicationLog对象
     * 
     * @param resultSet 结果集
     * @return 通联日志对象
     * @throws SQLException SQL异常
     */
    private CommunicationLog mapResultSetToLog(ResultSet resultSet) throws SQLException {
        CommunicationLog log = new CommunicationLog();
        
        log.setId(resultSet.getLong("id"));
        log.setSessionId(resultSet.getString("session_id"));
        log.setCallerId(resultSet.getString("caller_id"));
        log.setReceiverId(resultSet.getString("receiver_id"));
        
        Timestamp startTime = resultSet.getTimestamp("start_time");
        if (startTime != null) {
            log.setStartTime(startTime.toLocalDateTime());
        }
        
        Timestamp endTime = resultSet.getTimestamp("end_time");
        if (endTime != null) {
            log.setEndTime(endTime.toLocalDateTime());
        }
        
        log.setDurationSeconds(resultSet.getInt("duration_seconds"));
        log.setRecognitionText(resultSet.getString("recognition_text"));
        log.setConfidenceScore(resultSet.getDouble("confidence_score"));
        log.setAudioFilePath(resultSet.getString("audio_file_path"));
        log.setStatus(resultSet.getString("status"));
        log.setNotes(resultSet.getString("notes"));
        // 映射新增结构化字段
        try { log.setQth(resultSet.getString("qth")); } catch (SQLException ignored) {}
        try { log.setDeviceModel(resultSet.getString("device_model")); } catch (SQLException ignored) {}
        try { log.setSignalReport(resultSet.getString("signal_report")); } catch (SQLException ignored) {}
        try { log.setAntennaType(resultSet.getString("antenna_type")); } catch (SQLException ignored) {}
        try { double v = resultSet.getDouble("antenna_height_m"); if (!resultSet.wasNull()) log.setAntennaHeightMeters(v); } catch (SQLException ignored) {}
        
        Timestamp createdAt = resultSet.getTimestamp("created_at");
        if (createdAt != null) {
            log.setCreatedAt(createdAt.toLocalDateTime());
        }
        
        Timestamp updatedAt = resultSet.getTimestamp("updated_at");
        if (updatedAt != null) {
            log.setUpdatedAt(updatedAt.toLocalDateTime());
        }
        
        return log;
    }
    
    /**
     * 关闭数据源
     */
    public void close() {
        if (dataSource != null && !dataSource.isClosed()) {
            dataSource.close();
            logger.info("数据库连接池已关闭");
        }
    }
    
    /**
     * 检查数据库连接是否正常
     * 
     * @return true如果连接正常，false否则
     */
    public boolean isConnectionHealthy() {
        try (Connection connection = dataSource.getConnection()) {
            return connection.isValid(5);
        } catch (SQLException e) {
            logger.warn("数据库连接检查失败", e);
            return false;
        }
    }
}
